﻿package chen.com.yc.dao;

import java.io.BufferedReader;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

import org.hibernate.lob.BlobImpl;
import org.hibernate.lob.ClobImpl;

//import org.springframework.util.StreamUtils;
//
//import net.sourceforge.jtds.jdbc.BlobImpl;
//import net.sourceforge.jtds.jdbc.ClobImpl;

/**
 * 插入clob
 * @author 
 *
 */
public class Migration {
	
	public static void sqlserver(String tableName) {
		Connection scon = null;
		Connection ocon = null;
		PreparedStatement ps = null;
	  try {
		  ocon =  getOracleConn();
		  scon =  getSqlServerConn();
		  ocon.setAutoCommit(false);
		  String sql = "select * from " + tableName;
		  ps = scon.prepareStatement(sql);
		   ResultSet rs = ps.executeQuery();
		   ResultSetMetaData rsmd = rs.getMetaData();
		   while(rs.next()) {
			   Object[] os = new Object[rsmd.getColumnCount()];
			   StringBuilder columnSql =new StringBuilder("insert into " + tableName + " (");
			   StringBuilder valSql =new StringBuilder(" values(");
			   for (int i = 1; i <= rsmd.getColumnCount(); i++) {
				   String columnName = rsmd.getColumnName(i);
				   Object v =  rs.getObject(i);
				   if (v instanceof ClobImpl) {
					   ClobImpl clob = ClobImpl.class.cast(v);
					   BufferedReader in=new BufferedReader(clob.getCharacterStream());
                       StringWriter out=new StringWriter();
                       int c;
	                    while((c=in.read())!=-1){
	                      out.write(c);
	                    }
	                    os[i-1] = out.toString() == null || "".equals(out.toString()) ? (ResultSetMetaData.columnNoNulls == rsmd.isNullable(i)? " " : "") : out.toString();
	                    in.close();
	                    out.close();
				   } else if (v instanceof BlobImpl) {
//					   BlobImpl blob = BlobImpl.class.cast(v);
//					   os[i-1]  = StreamUtils.copyToByteArray(blob.getBinaryStream());
				   }else {
					   os[i-1] = rs.getObject(i) == null || "".equals(rs.getString(i)) ? (ResultSetMetaData.columnNoNulls == rsmd.isNullable(i)? " " : "") : rs.getObject(i);
				   }
				   columnSql.append(columnName);
				   valSql.append("?");
				   if (i != rsmd.getColumnCount()) {
					   columnSql.append(",");
					   valSql.append(",");
				   } else {
					   columnSql.append(")");
					   valSql.append(")");
				   }
			   }
			   String presql = columnSql.append(valSql).toString();
			   //PreparedStatement delete = ocon.prepareStatement("delete from " + tableName);
			   //delete.execute();
			   //delete.close();
			   //ocon.commit();
			   PreparedStatement ops = ocon.prepareStatement(presql);
			   for (int i = 1; i <= os.length; i++) {
				   ops.setObject(i, os[i-1]);
			   }
			   ops.execute();
			   ocon.commit();
			   ops.close();
		   }
		  } catch(Exception e) {
			  e.printStackTrace();
		  }  finally {
			  try {
				  if (ps != null) {
					  ps.close();
				  }
				  if (ps != null) {
						scon.close();
				  }
				  if (ps != null) {
						ocon.close();
				  }
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}  
	}
	
	public static Connection getOracleConn() throws SQLException, ClassNotFoundException {
		Class.forName("oracle.jdbc.driver.OracleDriver");
		return DriverManager.getConnection ("jdbc:oracle:thin:@172.16.1.5:1521:orcl","RDSYSEDUV807","eplugger");
	}
	
	public static Connection getSqlServerConn() throws ClassNotFoundException, SQLException {
		Class.forName("net.sourceforge.jtds.jdbc.Driver");
		return DriverManager.getConnection("jdbc:jtds:sqlserver://172.16.1.5:1433/RDSYSEDUV8_NEW","sa","Ep123!@#");
	}
  public static void main(String args[]) throws SQLException {
	  String ts = 	"SYS_GROUP," + 
			  		"SYS_USER," + 
					"SYS_MODULE," + 
					"SYS_VERSION_MANAGEMENT,"+
					"BIZ_NOTIFY," + 
					"SYS_APP_API," + 
					"SYS_CFG_EXPSQL," + 
					"SYS_CFG_FORM," +
					"KY_LXSRB_TRANS," + 
					"KY_LXYSB_TRANS," + 
					"KY_XMLX_TRANS," + 
					"SYS_IMAGE_IN_DB," + 
					"SYS_MENU_MENUSTANDARD," + 
					"SYS_STATISTIC," +
					"KY_PCH_YW_TRANS," +
					"KY_PCH_TRANS," +
					"KY_HTXX_TRANS," +
					"DM_QUICK_SEARCH," +
					"CW_BM";
	  for (String t: ts.split(","))  {
		  sqlserver(t);
	  }
  }
}
